from tool.file_path import *
from openpyxl import load_workbook
from sqlalchemy import create_engine
from data import *
import pandas as pd
import pymysql
import yaml
import json
import codecs
import os



# 操作excel，文件名需要加后缀（通过在read_excel方法中传入data下的文件名以及表标签名）
def read_excel(excel_file, sheet_name):
    value = []
    filepath = DATA_PATH + f"\{excel_file}"
    if not os.path.exists(filepath):
        raise ValueError("File not exists")
    wb = load_workbook(filepath)
    for s in wb.sheetnames:
        if s == sheet_name:
            sheet = wb[sheet_name]
            for row in sheet.rows:
                value.append([col.value for col in row])
            return value

# 操作txt(通过在read_txt方法中传入data文件夹下的文件名)
def read_txt(fileName):
    filepath = DATA_PATH + f"\{fileName}"
    arr = []
    with open(filepath, "r", encoding="utf-8") as file:
        datas = file.readlines()
        for data in datas:
            arr.append(tuple(data.strip().split(",")))
    return arr

# 操作json以及yaml（通过在read_json_or_yaml_file方法中传入data文件夹下的文件名）
def read_json_or_yaml_file(json_or_yaml_file):
    return_value = []
    file_path = DATA_PATH + f"\{json_or_yaml_file}"
    is_yaml_file = file_path.endswith((".yml", ".yaml"))
    with codecs.open(file_path, "r", encoding="utf-8") as file:
        if is_yaml_file:
            data = yaml.safe_load(file)
        else:    
            data = json.load(file)
    for i, element in enumerate(data):
        if isinstance(data, dict):
            key, value = element, data[element]
            # print(value)
            if isinstance(value, dict):
                case_data = []
                for v in value.values():
                    case_data.append(v)
                return_value.append(tuple(case_data))

            else:
                return_value.append((value))
    return return_value

# 操作数据库(sql_query用来存放查询语句的，datas是一个列表，用来存放字段名)
# 例如：参数化数据是name和age，那么查询语句就是select name,age from login，datas存放的就是["name","age"] 

def read_db(sql_query,datas):
    engine = create_engine(f"mysql+pymysql://{db_user}:{db_pwd}@{db_host}/{db_name}")
    with engine.connect() as conn:
        df = pd.read_sql_query(sql_query, conn)
    engine.dispose()
    return df[datas].values.tolist()